package net.risesoft.risebi.collection.dao.jdbc;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import net.risesoft.risebi.collection.dao.ReportInstanceR6Dao;
import net.risesoft.risebi.collection.model.ReportDataR6;
import net.risesoft.risebi.collection.model.ReportInstanceR6;

import org.appfuse.dao.jdbc.BaseDaoJdbc;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository(value = "reportInstanceR6Dao")
public class ReportInstanceR6DaoImpl extends BaseDaoJdbc implements ReportInstanceR6Dao{
	
	@Override
	@Transactional(readOnly = true)
	public ReportInstanceR6 getObject(String reportTaskID, Serializable id){
		String tableName = this.getReportTableName(reportTaskID);
		String sql = "select * from "+tableName+" where reportid=?";
		ReportInstanceR6 r6 = getSimpleJdbcTemplate().queryForObject(sql, ParameterizedBeanPropertyRowMapper.newInstance(ReportInstanceR6.class), id);
		r6.setTemplate_guid(this.getTemplate_guid(r6.getReportTemplateID()));
		return r6;
	}
	
	@Override
	@Transactional(readOnly = true)
    public List<ReportInstanceR6> getObjects(String reportTaskID){  
		String tableName = this.getReportTableName(reportTaskID);
		String sql = "select * from "+tableName;
        log.debug(sql);  
        return getSimpleJdbcTemplate()
            .query(sql, ParameterizedBeanPropertyRowMapper.newInstance(ReportInstanceR6.class));  
    }
    
	@Override
	public String getReportTableName(String reportTaskID)
			throws DataAccessException {
		String sql = "select tablename from Rpt_Report_table where reportTaskID=? ";
		String tableName = (String) this.getJdbcTemplate().queryForObject(sql,
				new Object[] { reportTaskID }, java.lang.String.class);
		return tableName;
	}
	
	public String getTemplate_guid(final String id) throws DataAccessException {
		String sql = "select template_guid from rpt_reporttemplate a where a.reportTemplateID=? ";
		return getSimpleJdbcTemplate().queryForObject(sql, java.lang.String.class, id);
	}
	
	@Override
	public  List<ReportDataR6>  getReportDatas(String reportTaskID, String reportID)
			throws DataAccessException {
		String sql = "select REPORTID,RangeCode,rowno,colno,ROWCode,COLCode,valuetype,valuestr from "
				+ this.getReportDataTableName(reportTaskID)
				+ " a "
				//+ " left join " + this.getReportTableName(reportTaskID)	+ " b on b.reportid=a.reportid "
				//+ " left join rpt_view_rowDefine c on c.reporttemplateid=b.reporttemplateid and c.rowcode=a.rowcode "
				//+ " left join rpt_brtcoldefine d on d.reporttemplateid=b.reporttemplateid and d.colcode=a.colcode "
				+ " where a.reportID = ? order by rangecode,rowCode,colCode ";
		log.debug(sql);
		List<ReportDataR6> list = getSimpleJdbcTemplate().query(sql, ParameterizedBeanPropertyRowMapper.newInstance(ReportDataR6.class),reportID);
		return list;
	}

	@Override
	public List<ReportDataR6> getReportDatas(String reportTaskID, String reportID,String rangeCode)
			throws DataAccessException {
		String sql = "select REPORTID,RangeCode,rowno,colno,ROWCode,COLCode,valuetype,valuestr from "
				+ this.getReportDataTableName(reportTaskID)
				+ " a "
				//+ " left join " + this.getReportTableName(reportTaskID)	+ " b on b.reportid=a.reportid "
				//+ " left join rpt_view_rowDefine c on c.reporttemplateid=b.reporttemplateid and c.rowcode=a.rowcode "
				//+ " left join rpt_brtcoldefine d on d.reporttemplateid=b.reporttemplateid and d.colcode=a.colcode "
				+ " where a.reportID = ? and a.rangeCode=? order by rangecode,rowCode,colCode ";
		log.debug(sql);
		List<ReportDataR6> list = this.getSimpleJdbcTemplate().query(sql, ParameterizedBeanPropertyRowMapper.newInstance(ReportDataR6.class),reportID,rangeCode);
		return list;
	}
	
	@Override
	public String getReportDataTableName(String reportTaskID)
			throws DataAccessException {
		String sql = "select tablename from Rpt_ReportData_table where reportTaskID=? ";

		String tableName = (String) this.getJdbcTemplate().queryForObject(sql,
				new Object[] { reportTaskID }, java.lang.String.class);

		return tableName;
	}
	
	public void updateReportDatas(String reportTaskID, String reportID,
			List<ReportDataR6> reportDatas) throws DataAccessException {
		String reportDataTableName = this.getReportDataTableName(reportTaskID);
		String sql = "update "
				+ reportDataTableName
				+ " set valuestr=?,valueType=? "
				+ " where reportID=? and rangecode=? and rowcode=? and colcode=?";
		List<ReportDataR6> tobeinsert = new ArrayList<ReportDataR6>();
		Iterator it = reportDatas.iterator();
		while (it.hasNext()) {
			ReportDataR6 data = (ReportDataR6) it.next();
			if(this.hasCellData(reportTaskID, reportID, data.getRangeCode(), data.getRowCode(), data.getColCode())){
				getJdbcTemplate().update(
						sql,
						new Object[] {
								data.getValueStr(),
								data.getValueType(), reportID, data.getRangeCode(),
								data.getRowCode(), data.getColCode() });
			}else{
				tobeinsert.add(data);
			}			
		}
		insertReportDatas( reportTaskID,reportID,tobeinsert);
	}
	
	public void insertReportDatas(String reportTaskID, String reportID,
			List<ReportDataR6> reportDatas) throws DataAccessException {
		String reportDataTableName = this.getReportDataTableName(reportTaskID);
		String sql = "insert into "
				+ reportDataTableName
				+ " (REPORTID,RangeCode,rowno,colno,ROWCode,COLCode,valuetype,valuestr) values(?,?,?,?,?,?,?,?)";
		Iterator it = reportDatas.iterator();
		while (it.hasNext()) {
			ReportDataR6 data = (ReportDataR6) it.next();
			getJdbcTemplate().update(
					sql,
					new Object[] {
							reportID,
							data.getRangeCode(),
							data.getRowNo(),
							data.getColNo(),
							data.getRowCode(),
							data.getColCode(),
							//data.getRowTitle(),
							//data.getColTitle(),
							//null,
							data.getValueType(), 
							//data.getUnit(),
							data.getValueStr() });
		}
	}
	
	@Override
	public boolean hasReportDatas(String reportTaskID, String reportID)
			throws DataAccessException {
		String sql = "select count(*) countx from "
				+ this.getReportDataTableName(reportTaskID) + " a "
				+ " where a.reportID = ?";
		int countx = getJdbcTemplate().queryForInt(sql,
				new Object[] { reportID });
		return countx > 0;
	}
	
	public void removeReportDatas(String reportTaskID, String reportID)
			throws DataAccessException {
		getJdbcTemplate().update(
				"delete from " + this.getReportDataTableName(reportTaskID)
						+ " where reportid=?", new Object[] { reportID });
	}
	
	@Override
	public void removeRangeDatas(String reportTaskID, String reportID,String rangeCode)
			throws DataAccessException {
		getJdbcTemplate().update(
				"delete from " + this.getReportDataTableName(reportTaskID)
						+ " where reportid=? and a.rangecode=? ", new Object[] { reportID,rangeCode });
	}
	
	@Override
	public void saveObject(ReportInstanceR6 r6)  throws DataAccessException {
		this.updateReportInfo(r6.getReportTaskID(), r6);
	}
	
	@Override
	public void saveReportDatas(String reportTaskID, String reportID,
			List<ReportDataR6> reportDatas) throws DataAccessException {
		if(hasReportDatas(reportTaskID, reportID)){
			this.updateReportDatas(reportTaskID, reportID, reportDatas);
		}else{
			this.insertReportDatas(reportTaskID, reportID, reportDatas);
		}
	}
	
	@Override
	public boolean hasRangeDatas(String reportTaskID, String reportID,String rangeCode)
			throws DataAccessException {
		String sql = "select count(*) countx from "
				+ this.getReportDataTableName(reportTaskID) + " a "
				+ " where a.reportID = ? and a.rangecode=?";
		int countx = getJdbcTemplate().queryForInt(sql,
				new Object[] { reportID,rangeCode });
		return countx > 0;
	}
	
	@Override
	public boolean hasRowDatas(String reportTaskID, String reportID,String rangeCode,String rowCode)
			throws DataAccessException {
		String sql = "select count(*) countx from "
				+ this.getReportDataTableName(reportTaskID) + " a "
				+ " where a.reportID = ? and a.rangecode=? and a.rowCode=?";
		int countx = getJdbcTemplate().queryForInt(sql,
				new Object[] { reportID,rangeCode,rowCode });
		return countx > 0;
	}
	

	public boolean hasCellData(String reportTaskID, String reportID,String rangeCode,String rowCode,String colCode)
			throws DataAccessException {
		String sql = "select count(*) countx from "
				+ this.getReportDataTableName(reportTaskID) + " a "
				+ " where a.reportID = ? and a.rangecode=? and a.rowCode=? and a.colcode=?";
		int countx = getJdbcTemplate().queryForInt(sql,
				new Object[] { reportID,rangeCode,rowCode,colCode });
		return countx > 0;
	}
	
	@Override
	public  ReportDataR6  getReportData(String reportTaskID, String reportID,String rangeCode,String rowCode)
			throws DataAccessException {
		String sql = "select * from "
				+ this.getReportDataTableName(reportTaskID)
				+ " a "
				+ " where a.reportID = ? and a.rangecode=? and a.rowCode=?";
		List<ReportDataR6> list = this.getSimpleJdbcTemplate().query(sql, ParameterizedBeanPropertyRowMapper.newInstance(ReportDataR6.class),reportID,rangeCode,rowCode);
		if ((list != null) && (list.size() > 0)) {
			return list.get(0);
		}else{
			return null;
		}
	}
	
	public void insertReportInfo(String reportTaskID, ReportInstanceR6 r6)
			throws DataAccessException {
		String sql = "insert into "
				+ this.getReportTableName(reportTaskID)
				+ "( reportTaskID,REPORTID,REPORTTEMPLATEID,MONTH,REGIONID,PROJECTID,"
				// "FIRSTDATE," +
				+ "CURRENTDATE," // 为保持rpt索引有效性，CURRENTDATE不能为空
				+ "ROWCOUNT,updateflag,LOCKSTATUS,WRITESTATUS,CHECKSTATUS,CACHESTATUS,REPORTFLAG)"
				+ "values(?,?,?,?,?,?,to_date('1999-09-09','YYYY-MM-DD'),?,'1',?,?,?,?,?)";

		getJdbcTemplate()
				.update(sql,
						new Object[] { r6.getReportTaskID(),
								r6.getReportID(),
								r6.getReportTemplateID(),
								String.valueOf(r6.getMonth()),
								r6.getRegionID(),
								r6.getProjectID(),
								String.valueOf(r6.getRowCount()),
								r6.getLockStatus(),
								r6.getWriteStatus(),
								r6.getCheckStatus(),
								r6.getCacheStatus(),
								r6.getReportFlag() });
	}
	
	public void removeReportInfo(String reportTaskID, String reportID)
			throws DataAccessException {
		// getJdbcTemplate().update(
		// "delete from " + this.getReportTableName(reportTaskID)
		// + " where reportid=?", new Object[] { reportID });
		getJdbcTemplate()
				.update("update "
						+ this.getReportTableName(reportTaskID)
						+ " set CACHESTATUS='0',WRITESTATUS='0',CHECKSTATUS='0',ROWCOUNT=0,"
						+ " MEMORY=null,FIRSTDATE=null,"
						+ " CURRENTDATE=to_date('1999-09-09','YYYY-MM-DD') " // 为了保持RPT索引有效，没有删除CURRENTDATE的值
						+ " where reportid=?", new Object[] { reportID });
	}
	
	public void updateReportInfo(String reportTaskID, ReportInstanceR6 r6)
			throws DataAccessException {
		getJdbcTemplate()
				.update("update "
						+ this.getReportTableName(reportTaskID)
						+ " set WRITESTATUS='1',CURRENTDATE=sysdate,memory=?,ROWCOUNT=?,CHECKSTATUS=? "
						+ " where reportid=?",
						new Object[] { r6.getMemory(),
								String.valueOf(r6.getRowCount()),
								r6.getCheckStatus(),
								r6.getReportID() });
	}
}
